/***************************************************************************************
Firm-embedded productivity and cross-country income differences
Alviarez, Cravino and Ramondo
Journal of Political Economy (2022)

Program: table_B2_affiliates_byisocodesector.do
Date: October 2022

Description: Reproduces Table B.2: Number of affiliates by country and sector.

*****************************************************************************************/

*-------------------------------------------------------------------------------
global typeden=1
include "set_directories.do"
set memory 64g
global lf "LF"

*Log
cap log close
log using "${clogs}/table_B2_affiliates_byisocodesector.log",replace
*-------------------------------------------------------------------------------


clear all
use "${data}/sin_firmlevel_naics_gravityLF.dta", clear

keep if isocode=="DK" | isocode=="JP" | isocode=="IT" | isocode=="KR" | isocode=="DE" | isocode=="FR" | isocode=="ES" |  isocode=="MX" |  isocode=="GB" |  isocode=="PL" |  isocode=="GR" |  isocode=="NL" |  isocode=="RO" |  isocode=="BE" |  isocode=="AT" |  isocode=="PT" |  isocode=="FI" |  isocode=="SE" |  isocode=="CZ" |  isocode=="SK" |  isocode=="BG" |  isocode=="HU" |  isocode=="HR" |  isocode=="LV" |  isocode=="SI" |  isocode=="LT" |  isocode=="EE" 
keep if year==2016
drop if sin_sales==. | sin_sales==0

*keep MNCs only
by year sector guo_bvd isocode, sort: gen a=_n==1
replace a=0 if isocode==hq
by year sector guo_bvd, sort: egen b=total(a)
tab b 
keep if b>=2
drop a b 
tab isocode 
keep if isocode!=hq

tempfile temp
save `temp', replace


*Number of foreign affiliates in all sectors 
use `temp', clear 
collapse (count) sales_=sin_sales emp_=sin_emp va_=sin_va, by(isocode year)
gen sector="all"
tempfile temp_tot
save `temp_tot', replace 


*Number of foreign affiliates in manufacturing and services  
use `temp', clear 
keep if sector1=="Manufacturing (C)" | sector1=="Market_Services (G-H-I-J-K-M-N-R-S-T)"
drop sector
gen sector="manufacturing" if sector1=="Manufacturing (C)"
replace sector="services" if sector1=="Market_Services (G-H-I-J-K-M-N-R-S-T)"
collapse (count) sales_=sin_sales emp_=sin_emp va_=sin_va, by(isocode year sector)
tempfile temp_manufserv
save `temp_manufserv', replace 


*Append total, manuf and services 
use `temp_manufserv', clear
append using `temp_tot'

reshape wide sales_ emp_ va_, i(year isocode) j(sector) string 
order year isocode sales_* emp_* va_*
format %9.0fc sales_* emp_* va_*

export delimited using "${rappendix}/table_B2_affiliates_byisocodesector.csv", datafmt replace

log close 